
Imports System
Imports System.Collections
Imports System.Drawing
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports _2bMatesSite.Common


Namespace _2bMatesSite.BO
	''' <summary>
	''' Represents a user's picture object. Has some additional functionality (supports comments)
	''' </summary>
	Public Class UserPicture
		Inherits Picture
		Private userID As Integer

		'
		' Constructors
		'

		' Creates a new image
		Public Sub New(ByVal usr As User, ByVal img As Bitmap, ByVal name As String, ByVal desc As String)
			MyBase.New(img, name, desc)
			userID = usr.ID
		End Sub

		Public Sub New(ByVal dr As SqlDataReader)
			MyBase.New(dr)
		End Sub

		'
		' Specific public properties
		'

		Public ReadOnly Property UserID() As Integer
			Get
				Return userID
			End Get
		End Property

		Public ReadOnly Property User() As User
			Get
				Return Users.GetUserByID(userID)
			End Get
		End Property

		'
		' Comments-related functionality, for now only present in user's pictures (not in Group's pics)
		'

		Public Sub AddComment(ByVal fromUserID As Integer, ByVal comment As String, ByVal comStatus As PictureComment.Status)
			Dim picComment As New PictureComment(id, fromUserID, comment)
			picComment.CommentStatus = comStatus
			AddComment(picComment)
		End Sub

		Public Sub AddComment(ByVal comment As PictureComment)
			If comment.PictureID <> id Then
				Throw New Exception("Can't add comments to a picture about other pictures.")
			End If

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim sql As String = "INSERT INTO PictureComments (Comment, UID, PictureID, Status, Date)" + Environment.NewLine
				sql += "VALUES (@Comment, @UID, @PictureID, @Status, @Date)" + Environment.NewLine

				Dim cmd As New SqlCommand(sql)
				cmd.Connection = conn
				cmd.Parameters.Add("@Comment", comment.Comment)
				cmd.Parameters.Add("@UID", comment.PostedByUserID)
				cmd.Parameters.Add("@PictureID", comment.PictureID)
				cmd.Parameters.Add("@Status", DirectCast(comment.CommentStatus, Integer))
				cmd.Parameters.Add("@Date", comment.[Date])

				cmd.ExecuteNonQuery()
			Finally
				conn.Close()
			End Try
		End Sub

		Public Sub RemoveComment(ByVal commentID As Integer)
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim sql As String = "DELETE FROM PictureComments WHERE ID=@ID AND PictureID=@PictureID" + Environment.NewLine
				Dim cmd As New SqlCommand(sql)
				cmd.Connection = conn

				cmd.Parameters.Add("@ID", commentID)
				cmd.Parameters.Add("@PictureID", id)

				cmd.ExecuteNonQuery()
			Finally
				conn.Close()
			End Try
		End Sub

		Public Sub ApproveComment(ByVal commentID As Integer)
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim sql As String = "UPDATE PictureComments SET Status=" + DirectCast(PictureComment.Status.pcsAccepted, Integer) + " WHERE ID=@ID AND PictureID=@PictureID" + Environment.NewLine
				Dim cmd As New SqlCommand(sql)
				cmd.Connection = conn

				cmd.Parameters.Add("@ID", commentID)
				cmd.Parameters.Add("@PictureID", id)

				cmd.ExecuteNonQuery()
			Finally
				conn.Close()
			End Try
		End Sub

		Public Sub ApproveComment(ByVal commentID As Integer, ByVal bDoNotApproveNewAcceptedComments As Boolean)
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim sql As String = "UPDATE PictureComments SET Status=" + DirectCast(PictureComment.Status.pcsAccepted, Integer) + " WHERE ID=@ID AND PictureID=@PictureID" + Environment.NewLine
				If bDoNotApproveNewAcceptedComments = True Then
					sql = "UPDATE PictureComments SET Status=" + DirectCast(PictureComment.Status.pcsAccepted, Integer) + " WHERE ID=@ID AND PictureID=@PictureID AND Status<>" + DirectCast(PictureComment.Status.pcsNewAccepted, Integer) + Environment.NewLine
				End If

				Dim cmd As New SqlCommand(sql)
				cmd.Connection = conn

				cmd.Parameters.Add("@ID", commentID)
				cmd.Parameters.Add("@PictureID", id)

				cmd.ExecuteNonQuery()
			Finally
				conn.Close()
			End Try
		End Sub

		' This function is used to set all comments as read, when the user has set ApproveComments = true
		Public Sub ApproveAllNewPictureComments()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim sql As String = "UPDATE PictureComments SET Status=" + DirectCast(PictureComment.Status.pcsAccepted, Integer) + " WHERE PictureID=@PictureID" + Environment.NewLine + " AND Status=" + DirectCast(PictureComment.Status.pcsNewAccepted, Integer)

				Dim cmd As New SqlCommand(sql)
				cmd.Connection = conn

				cmd.Parameters.Add("@PictureID", id)

				cmd.ExecuteNonQuery()
			Finally
				conn.Close()
			End Try
		End Sub

		Public ReadOnly Property NewComments() As ArrayList
			Get
				Return GetNewComments()
			End Get
		End Property

		Public ReadOnly Property Comments() As ArrayList
			Get
				Return GetApprovedComments()
			End Get
		End Property


		'
		' Private and protected methods
		'

		Protected Function GetNewComments() As ArrayList
			Dim cl As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
					'"SELECT * FROM PictureComments WHERE PictureID=@PictureID AND Status="
					'+ (int)PictureComment.Status.pcsNew + " ORDER BY DATE DESC" );

				Dim cmd As New SqlCommand("SELECT * FROM PictureComments WHERE PictureID=@PictureID AND Status IN (" + DirectCast(PictureComment.Status.pcsNew, Integer) + ", " + DirectCast(PictureComment.Status.pcsNewAccepted, Integer) + ") ORDER BY DATE DESC")
				cmd.Connection = conn
				cmd.Parameters.Add("@PictureID", id)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					cl.Add(New PictureComment(dr))
				End While
				dr.Close()
			Finally
				conn.Close()
			End Try

			Return cl
		End Function

		Protected Function GetApprovedComments() As ArrayList
			Dim cl As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM PictureComments WHERE PictureID=@PictureID AND Status=" + DirectCast(PictureComment.Status.pcsAccepted, Integer) + " ORDER BY DATE DESC")
				cmd.Connection = conn
				cmd.Parameters.Add("@PictureID", id)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					cl.Add(New PictureComment(dr))
				End While
				dr.Close()
			Finally
				conn.Close()
			End Try

			Return cl
		End Function

		'
		' Private and protected methods
		'

		Protected Overloads Overrides Sub LoadFromReader(ByVal dr As SqlDataReader)
			id = dr.GetInt32(dr.GetOrdinal("ID"))
			userID = dr.GetInt32(dr.GetOrdinal("UID"))
			name = DBManager.GetNZString(dr, "Name")
			desc = DBManager.GetNZString(dr, "Description")
			img = Nothing
		End Sub

		Protected Overloads Overrides Function GetImageDataOrdinal(ByVal dr As SqlDataReader) As Integer
			Return dr.GetOrdinal("Picture")
		End Function

		Protected Overloads Overrides Function GetLoadSQLCommand() As SqlCommand
			Dim cmd As New SqlCommand("SELECT Picture FROM Pictures WHERE ID=@ID")
			Return cmd
		End Function

		Protected Overloads Overrides Function GetInsertSQLCommand() As SqlCommand
			Dim cmd As New SqlCommand("SavePicture")
			cmd.CommandType = CommandType.StoredProcedure
			Return cmd
		End Function

		Protected Overloads Overrides Function GetUpdateSQLCommand() As SqlCommand
			Dim cmd As New SqlCommand(GetUpdateSQL())
			Return cmd
		End Function

		Private Function GetUpdateSQL() As String
			Dim sql As String = "UPDATE Pictures SET" + Environment.NewLine
			sql += "" & Chr(9) & "UID=@UID," + Environment.NewLine
			If img <> Nothing Then
				sql += "" & Chr(9) & "Picture=@Picture," + Environment.NewLine
			End If
			sql += "" & Chr(9) & "Name=@Name," + Environment.NewLine
			sql += "" & Chr(9) & "Description=@Description" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
			Return sql
		End Function

		Protected Overloads Overrides Sub PopulateCommand(ByVal cmd As SqlCommand, ByVal forcePopulatePicture As Boolean)
			cmd.Parameters.Add("@UID", userID)

			DBManager.SetNZValue(cmd, "@Name", name)
			DBManager.SetNZValue(cmd, "@Description", desc)

			If img <> Nothing Then
				Dim memStream As New MemoryStream()
				img.Save(memStream, img.RawFormat)
				memStream.Flush()

				Dim imgBytes As Byte() = memStream.GetBuffer()

				cmd.Parameters.Add("@Picture", SqlDbType.Image, imgBytes.Length).Value = imgBytes
ElseIf forcePopulatePicture Then
				cmd.Parameters.Add("@Picture", SqlDbType.Image).Value = DBNull.Value
			End If
		End Sub
	End Class
End Namespace






